package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import domain.BioRecord;
import domain.DelRecord;
import domain.InsRecord;
import domain.PolRecord;
import domain.RpoRecord;
import domain.result.Result;

public class ResultDao {
	
	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	
	public boolean saveResult(int fileNo, Result result){
		boolean success = true;
		
		String query = "INSERT INTO dbo.DVIP_OUT_RESULT (File_No, Label, Value) VALUES (?, ?, ?)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			prepStmt = sqlConnection.prepareStatement(query);
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cwt");
			prepStmt.setDouble(3, result.getCwt());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "eaa");
			prepStmt.setDouble(3, result.getEaa());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "milkYield");
			prepStmt.setDouble(3, result.getMilkYield());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "milkSales");
			prepStmt.setDouble(3, result.getMilkSales());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "feedCost");
			prepStmt.setDouble(3, result.getFeedCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "heiferCost");
			prepStmt.setDouble(3, result.getHeiferCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cowSales");
			prepStmt.setDouble(3, result.getCowSales());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "calfSales");
			prepStmt.setDouble(3, result.getCalfSales());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "liveStockCost");
			prepStmt.setDouble(3, result.getLiveStockCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "laborCost");
			prepStmt.setDouble(3, result.getLaborCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "vetCost");
			prepStmt.setDouble(3, result.getVetCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "breedingSupplyCost");
			prepStmt.setDouble(3, result.getBreedingSupplyCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "loss");
			prepStmt.setDouble(3, result.getLoss());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "fixedCost_Labor");
			prepStmt.setDouble(3, result.getFixedCost_Labor());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "fixedCost_Other");
			prepStmt.setDouble(3, result.getFixedCost_Other());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "otherCost");
			prepStmt.setDouble(3, result.getOtherCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "breedingCost");
			prepStmt.setDouble(3, result.getBreedingCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "feedCostDry");
			prepStmt.setDouble(3, result.getFeedCostDry());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "feedCostLac");
			prepStmt.setDouble(3, result.getFeedCostLac());
			prepStmt.addBatch();
			
			
			
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "herdLife");
			prepStmt.setDouble(3, result.getHerdLife());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cash");
			prepStmt.setDouble(3, result.getCash());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "clvInterval");
			prepStmt.setDouble(3, result.getClvInterval());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "daysToConc");
			prepStmt.setDouble(3, result.getDaysToConc());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cullRateAll");
			prepStmt.setDouble(3, result.getCullRateAll());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cullRateVol");
			prepStmt.setDouble(3, result.getCullRateVol());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cullRateInv");
			prepStmt.setDouble(3, result.getCullRateInv());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "pregnancyRate0");
			prepStmt.setDouble(3, result.getPregnancyRate0());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "pregnancyRate1");
			prepStmt.setDouble(3, result.getPregnancyRate1());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "daysOpen");
			prepStmt.setDouble(3, result.getDaysOpen());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "pregLaborCost");
			prepStmt.setDouble(3, result.getPregLaborCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "pregSupplyCost");
			prepStmt.setDouble(3, result.getPregSupplyCost());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "pv_Open");
			prepStmt.setDouble(3, result.getPv_Open());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "inseminationValue");
			prepStmt.setDouble(3, result.getInseminationValue());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "openDays");
			prepStmt.setDouble(3, result.getOpenDays());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "abortRisk");
			prepStmt.setDouble(3, result.getAbortRisk());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "pv_Preg");
			prepStmt.setDouble(3, result.getPv_Preg());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cowValue");
			prepStmt.setDouble(3, result.getCowValue());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "dimY");
			prepStmt.setDouble(3, result.getDimY());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "dfs");
			prepStmt.setDouble(3, result.getDfs());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "cullPriceY");
			prepStmt.setDouble(3, result.getCullPriceY());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "replOpen_Mlk");
			prepStmt.setDouble(3, result.getReplOpen_Mlk());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "replOpen_Cur");
			prepStmt.setDouble(3, result.getReplOpen_Cur());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "replOpen_Rev");
			prepStmt.setDouble(3, result.getReplOpen_Rev());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "replOpen_Iof");
			prepStmt.setDouble(3, result.getReplOpen_Iof());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "replOpen_Rpo");
			prepStmt.setDouble(3, result.getReplOpen_Rpo());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "replOpen_Dim");
			prepStmt.setDouble(3, result.getReplOpen_Dim());
			prepStmt.addBatch();
			
			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "dvwp");
			prepStmt.setDouble(3, result.getDvwp());
			prepStmt.addBatch();

			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "dlbm");
			prepStmt.setDouble(3, result.getDlbm());
			prepStmt.addBatch();

			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "daysOpen_Cost");
			prepStmt.setDouble(3, result.getDaysOpen_Cost());
			prepStmt.addBatch();

			prepStmt.setInt(1, fileNo);
			prepStmt.setString(2, "daysPreg_Cost");
			prepStmt.setDouble(3, result.getDaysPreg_Cost());
			prepStmt.addBatch();
			
			
			prepStmt.executeBatch();
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			success = false;
			e.printStackTrace();
		}
		
		return success;
	}
	
	public int insertPolRecord(int fileNo, PolRecord pol){
		int result = 1;
		
		String query = "INSERT INTO dbo.DVIP_OUT_POL_DATA (File_No, Season, Current_Milk_Level, Lactation_No, Months_Pregnant, MIM1, MIM2, MIM3, MIM4, MIM5, MIM6, MIM7, MIM8, MIM9, MIM10, MIM11, MIM12, MIM13, MIM14, MIM15, MIM16, MIM17, MIM18, MIM19, MIM20, MIM21, MIM22, MIM23, MIM24) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			sqlConnection.setAutoCommit(false);
			prepStmt = sqlConnection.prepareStatement(query);
			
			/**
			 * Entering Row 0 containing Enter Heifers data with SEA = CURR = LACT = MPREG = 0
			 */
			int colIndex = 1;
			prepStmt.setInt(colIndex++, fileNo);
			prepStmt.setInt(colIndex++, 0);
			prepStmt.setInt(colIndex++, 0);
			prepStmt.setInt(colIndex++, 0);
			prepStmt.setInt(colIndex++, 0);
			for(int sea = 1; sea <= 2 * Constants.SEA; sea++){
				prepStmt.setInt(colIndex++, pol.getEnter()[sea]);
			}
			prepStmt.addBatch();
			
			/**
			 * Entering rest of the data 
			 */
			
			for(int sea = 1; sea <= Constants.SEA; sea++){
				for(int curr = 1; curr <= Constants.CURR; curr++){
					for(int lact = 1; lact <= Constants.LACT; lact++){
						for(int mpreg = 0; mpreg <= Constants.MPREG; mpreg++){
							colIndex = 1;
							prepStmt.setInt(colIndex++, fileNo);
							prepStmt.setInt(colIndex++, sea);
							prepStmt.setInt(colIndex++, curr);
							prepStmt.setInt(colIndex++, lact);
							prepStmt.setInt(colIndex++, mpreg);
							for(int mim = 1; mim <= Constants.MIM; mim++){
								prepStmt.setInt(colIndex++, pol.getCow()[curr][lact][mim][mpreg][sea]);
							}
							prepStmt.addBatch();
						}
					}
				}
			}
			
			int insertStatus[] = prepStmt.executeBatch();
			
			for (int i = 0; i < insertStatus.length; i++) {
				if(insertStatus[i]<=0){
					result = 0;
				}
			}
			
			if(result==1){
				sqlConnection.commit();
			}else{
				sqlConnection.rollback();
				System.out.println("Inside insertPolRecord -- Query was rollbacked");
			}
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public int insertRpoRecord(int fileNo, BioRecord bio, RpoRecord rpo, InsRecord ins){
		int result = 1;
		
		String queryRpo = "INSERT INTO dbo.DVIP_OUT_RPO_DATA (File_No, Current_Milk_Level, Lactation_No, Months_In_Milk, Months_Pregnant, Open_Or_Preg, Season1, Season2, Season3, Season4, Season5, Season6, Season7, Season8, Season9, Season10, Season11, Season12) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String queryIns = "INSERT INTO dbo.DVIP_OUT_INS_DATA (File_No, Current_Milk_Level, Lactation_No, Months_In_Milk, Season1, Season2, Season3, Season4, Season5, Season6, Season7, Season8, Season9, Season10, Season11, Season12) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String queryBioMilkYield = "INSERT INTO dbo.DVIP_OUT_BIO_MILKYIELD_DATA (File_No, Current_Milk_Level, Lactation_No, Months_In_Milk, Months_Pregnant, Season1, Season2, Season3, Season4, Season5, Season6, Season7, Season8, Season9, Season10, Season11, Season12) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		String queryBioSell = "INSERT INTO dbo.DVIP_OUT_BIO_SELL_DATA (File_No, Lactation_No, Months_In_Milk, Season1, Season2, Season3, Season4, Season5, Season6, Season7, Season8, Season9, Season10, Season11, Season12) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmtRpo = null;
		PreparedStatement prepStmtIns = null;
		PreparedStatement prepStmtBioMilkYield = null;
		PreparedStatement prepStmtBioSell = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			sqlConnection.setAutoCommit(false);
			prepStmtRpo = sqlConnection.prepareStatement(queryRpo);
			prepStmtIns = sqlConnection.prepareStatement(queryIns);
			prepStmtBioMilkYield = sqlConnection.prepareStatement(queryBioMilkYield);
			prepStmtBioSell = sqlConnection.prepareStatement(queryBioSell);
			int colIndex = 1;
			
			
			/**
			 * Adding RPO Data
			 */
			for(int curr = 1; curr <= Constants.CURR; curr++){
				for(int lact = 1; lact <= Constants.LACT; lact++){
					for(int mim = 1; mim <= Constants.MIM; mim++){
						for(int mpreg = 0; mpreg <= Constants.MPREG; mpreg++){
							if(mpreg == 0 || (mim - mpreg >= 2 && mim - mpreg <= 15)){
									
								/**
								 * Adding Cow Data
								 */
								colIndex = 1;
								prepStmtRpo.setInt(colIndex++, fileNo);
								prepStmtRpo.setInt(colIndex++, curr);
								prepStmtRpo.setInt(colIndex++, lact);
								prepStmtRpo.setInt(colIndex++, mim);
								prepStmtRpo.setInt(colIndex++, mpreg);
								prepStmtRpo.setInt(colIndex++, Constants.OPEN_COW);
								for(int sea = 1; sea <= Constants.SEA; sea++){
									prepStmtRpo.setDouble(colIndex++, rpo.getCow()[curr][lact][mim][mpreg][sea]);
								}
								prepStmtRpo.addBatch();
								
								if(mim - mpreg >= 2 && mim - mpreg <= 16){
									/**
									 * Adding Preg Data
									 */
									if(mim!=1 && mim!=2){
										colIndex = 1;
										prepStmtRpo.setInt(colIndex++, fileNo);
										prepStmtRpo.setInt(colIndex++, curr);
										prepStmtRpo.setInt(colIndex++, lact);
										prepStmtRpo.setInt(colIndex++, mim);
										prepStmtRpo.setInt(colIndex++, mpreg);
										prepStmtRpo.setInt(colIndex++, Constants.PREG_COW);
										for(int sea = 1; sea <= Constants.SEA; sea++){
											prepStmtRpo.setDouble(colIndex++, rpo.getPrg()[curr][lact][mim][mpreg][sea]);
										}
										prepStmtRpo.addBatch();
									}
								}
							}
						}
					}
				}
			}
			
			
			/**
			 * Adding INS Data
			 */
			for(int curr = 1; curr <= Constants.CURR; curr++){
				for(int lact = 1; lact <= Constants.LACT; lact++){
					for(int mim = 1; mim <= Constants.MIM; mim++){
						if(mim >= 2 && mim <= 15){
							colIndex = 1;
							prepStmtIns.setInt(colIndex++, fileNo);
							prepStmtIns.setInt(colIndex++, curr);
							prepStmtIns.setInt(colIndex++, lact);
							prepStmtIns.setInt(colIndex++, mim);
							for(int sea = 1; sea <= Constants.SEA; sea++){
								prepStmtIns.setDouble(colIndex++, ins.getCow()[curr][lact][mim][sea]);
							}
							prepStmtIns.addBatch();
						}
					}
				}
			}
			
			
			/**
			 * Adding Bio MIlkYield Data
			 */
			for(int curr = 1; curr <= Constants.CURR; curr++){
				for(int lact = 1; lact <= Constants.LACT; lact++){
					for(int mim = 1; mim <= Constants.MIM; mim++){
						for(int mpreg = 0; mpreg <= Constants.MPREG; mpreg++){
							if(mpreg == 0 || (mim - mpreg >= 2 && mim - mpreg <= 15)){
								colIndex = 1;
								prepStmtBioMilkYield.setInt(colIndex++, fileNo);
								prepStmtBioMilkYield.setInt(colIndex++, curr);
								prepStmtBioMilkYield.setInt(colIndex++, lact);
								prepStmtBioMilkYield.setInt(colIndex++, mim);
								prepStmtBioMilkYield.setInt(colIndex++, mpreg);
								for(int sea = 1; sea <= Constants.SEA; sea++){
									prepStmtBioMilkYield.setDouble(colIndex++, bio.getMilkyield()[curr][lact][mim][mpreg][sea] / Constants.STAGE);
								}
								prepStmtBioMilkYield.addBatch();
							}
						}
					}
				}
			}
			
			
			/**
			 * Adding Bio Sell Data
			 */
			for(int lact = 1; lact <= Constants.LACT; lact++){
				for(int mim = 1; mim <= Constants.MIM; mim++){
					colIndex = 1;
					prepStmtBioSell.setInt(colIndex++, fileNo);
					prepStmtBioSell.setInt(colIndex++, lact);
					prepStmtBioSell.setInt(colIndex++, mim);
					for(int sea = 1; sea <= Constants.SEA; sea++){
						prepStmtBioSell.setDouble(colIndex++, bio.getSel()[lact][mim][sea]);
					}
					prepStmtBioSell.addBatch();
				}
			}

			int insertRpoStatus[] = prepStmtRpo.executeBatch();
			int insertInsStatus[] = prepStmtIns.executeBatch();
			int insertBioMilkYieldStatus[] = prepStmtBioMilkYield.executeBatch();
			int insertBioSellStatus[] = prepStmtBioSell.executeBatch();
			
			int resultRpo = 1;
			int resultIns = 1;
			int resultBioMilkYield = 1;
			int resultBioSell = 1;
			
			for (int i = 0; i < insertRpoStatus.length; i++) {
				if(insertRpoStatus[i]<=0){
					resultRpo = 0;
				}
			}
			for (int i = 0; i < insertInsStatus.length; i++) {
				if(insertInsStatus[i]<=0){
					resultIns = 0;
				}
			}
			for (int i = 0; i < insertBioMilkYieldStatus.length; i++) {
				if(insertBioMilkYieldStatus[i]<=0){
					resultBioMilkYield = 0;
				}
			}
			for (int i = 0; i < insertBioSellStatus.length; i++) {
				if(insertBioSellStatus[i]<=0){
					resultBioSell = 0;
				}
			}
			
			result = resultRpo + resultIns + resultBioMilkYield + resultBioSell;
			
			if(resultRpo==1 && resultIns==1 && resultBioMilkYield==1 && resultBioSell==1){
				sqlConnection.commit();
			}else{
				sqlConnection.rollback();
				System.out.println("Inside insertRpoRecord -- Query was rollbacked");
			}
			
			prepStmtRpo.close();
			prepStmtIns.close();
			prepStmtBioMilkYield.close();
			prepStmtBioSell.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Exception in insertRpoRecord -- Query was not executed");
		}
		
		return result;
	}
	
	public int insertDelRecord(int fileNo, DelRecord del){
		int result = 1;
		
		String query = "INSERT INTO dbo.DVIP_OUT_DEL_DATA (File_No, Current_Milk_Level, Lactation_No, Months_In_Milk, Months, Open_Or_Preg, Season1, Season2, Season3, Season4, Season5, Season6, Season7, Season8, Season9, Season10, Season11, Season12) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			sqlConnection.setAutoCommit(false);
			prepStmt = sqlConnection.prepareStatement(query);
			int colIndex;
			
			for(int curr = 1; curr <= Constants.CURR; curr++){
				for(int lact = 1; lact <= Constants.LACT; lact++){
					for(int mim = 1; mim <= Constants.MIM; mim++){
						for(int m = mim + 1; m <= Constants.MIM; m++){
							/**
							 * Values for Open Cows
							 */
							colIndex = 1;
							prepStmt.setInt(colIndex++, fileNo);
							prepStmt.setInt(colIndex++, curr);
							prepStmt.setInt(colIndex++, lact);
							prepStmt.setInt(colIndex++, mim);
							prepStmt.setInt(colIndex++, m);
							prepStmt.setInt(colIndex++, Constants.OPEN_COW);
							for(int sea = 1; sea <= Constants.SEA; sea++){
								prepStmt.setDouble(colIndex++, del.getOpen()[curr][lact][mim][m][sea]);
							}
							prepStmt.addBatch();
							
							/**
							 * Values for Preg Cows
							 */
							if((mim!=1 && mim!=2) && (4<=m && m<=16)){
								colIndex = 1;
								prepStmt.setInt(colIndex++, fileNo);
								prepStmt.setInt(colIndex++, curr);
								prepStmt.setInt(colIndex++, lact);
								prepStmt.setInt(colIndex++, mim);
								prepStmt.setInt(colIndex++, m);
								prepStmt.setInt(colIndex++, Constants.PREG_COW);
								for(int sea = 1; sea <= Constants.SEA; sea++){
									prepStmt.setDouble(colIndex++, del.getPreg()[curr][lact][mim][m][sea]);
								}
								prepStmt.addBatch();	
							}
						}
					}
				}
			}
			
			int insertStatus[] = prepStmt.executeBatch();
			
			for (int i = 0; i < insertStatus.length; i++) {
				if(insertStatus[i]<=0){
					result = 0;
				}
			}
			
			if(result==1){
				sqlConnection.commit();
			}else{
				sqlConnection.rollback();
				System.out.println("Inside insertDelRecord -- Query was rollbacked");
			}
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	public Result getResult(int fileNo){
		Result result = new Result();
		
		String query = "SELECT Label, Value FROM dbo.DVIP_OUT_RESULT WHERE File_No = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		ResultSet  rs = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			prepStmt = sqlConnection.prepareStatement(query);
			
			prepStmt.setInt(1, fileNo);
			rs = prepStmt.executeQuery();
			
			String label;
			double value;
			
			while (rs.next()) {
				label = rs.getString("Label");
				value = rs.getDouble("Value");
				
				if (label.equalsIgnoreCase("cwt")) {
					result.setCwt(value);
				}
				
				if (label.equalsIgnoreCase("eaa")) {
					result.setEaa(value);
				}

				if (label.equalsIgnoreCase("milkYield")) {
					result.setMilkYield(value);
				}

				if (label.equalsIgnoreCase("milkSales")) {
					result.setMilkSales(value);
				}

				if (label.equalsIgnoreCase("feedCost")) {
					result.setFeedCost(value);
				}

				if (label.equalsIgnoreCase("heiferCost")) {
					result.setHeiferCost(value);
				}

				if (label.equalsIgnoreCase("cowSales")) {
					result.setCowSales(value);
				}

				if (label.equalsIgnoreCase("calfSales")) {
					result.setCalfSales(value);
				}

				if (label.equalsIgnoreCase("liveStockCost")) {
					result.setLiveStockCost(value);
				}

				if (label.equalsIgnoreCase("laborCost")) {
					result.setLaborCost(value);
				}

				if (label.equalsIgnoreCase("vetCost")) {
					result.setVetCost(value);
				}

				if (label.equalsIgnoreCase("breedingSupplyCost")) {
					result.setBreedingSupplyCost(value);
				}

				if (label.equalsIgnoreCase("loss")) {
					result.setLoss(value);
				}

				if (label.equalsIgnoreCase("fixedCost_Labor")) {
					result.setFixedCost_Labor(value);
				}

				if (label.equalsIgnoreCase("fixedCost_Other")) {
					result.setFixedCost_Other(value);
				}

				if (label.equalsIgnoreCase("otherCost")) {
					result.setOtherCost(value);
				}

				if (label.equalsIgnoreCase("breedingCost")) {
					result.setBreedingCost(value);
				}

				if (label.equalsIgnoreCase("feedCostDry")) {
					result.setFeedCostDry(value);
				}

				if (label.equalsIgnoreCase("feedCostLac")) {
					result.setFeedCostLac(value);
				}

				if (label.equalsIgnoreCase("herdLife")) {
					result.setHerdLife(value);
				}

				if (label.equalsIgnoreCase("cash")) {
					result.setCash(value);
				}

				if (label.equalsIgnoreCase("clvInterval")) {
					result.setClvInterval(value);
				}

				if (label.equalsIgnoreCase("daysToConc")) {
					result.setDaysToConc(value);
				}

				if (label.equalsIgnoreCase("cullRateAll")) {
					result.setCullRateAll(value);
				}

				if (label.equalsIgnoreCase("cullRateVol")) {
					result.setCullRateVol(value);
				}

				if (label.equalsIgnoreCase("cullRateInv")) {
					result.setCullRateInv(value);
				}

				if (label.equalsIgnoreCase("pregnancyRate0")) {
					result.setPregnancyRate0(value);
				}

				if (label.equalsIgnoreCase("pregnancyRate1")) {
					result.setPregnancyRate1(value);
				}

				if (label.equalsIgnoreCase("daysOpen")) {
					result.setDaysOpen(value);
				}

				if (label.equalsIgnoreCase("pregLaborCost")) {
					result.setPregLaborCost(value);
				}

				if (label.equalsIgnoreCase("pregSupplyCost")) {
					result.setPregSupplyCost(value);
				}

				if (label.equalsIgnoreCase("pv_Open")) {
					result.setPv_Open(value);
				}

				if (label.equalsIgnoreCase("inseminationValue")) {
					result.setInseminationValue(value);
				}

				if (label.equalsIgnoreCase("openDays")) {
					result.setOpenDays(value);
				}

				if (label.equalsIgnoreCase("abortRisk")) {
					result.setAbortRisk(value);
				}

				if (label.equalsIgnoreCase("pv_Preg")) {
					result.setPv_Preg(value);
				}

				if (label.equalsIgnoreCase("cowValue")) {
					result.setCowValue(value);
				}

				if (label.equalsIgnoreCase("dimY")) {
					result.setDimY(value);
				}

				if (label.equalsIgnoreCase("dfs")) {
					result.setDfs(value);
				}

				if (label.equalsIgnoreCase("cullPriceY")) {
					result.setCullPriceY(value);
				}

				if (label.equalsIgnoreCase("replOpen_Mlk")) {
					result.setReplOpen_Mlk(value);
				}

				if (label.equalsIgnoreCase("replOpen_Cur")) {
					result.setReplOpen_Cur(value);
				}

				if (label.equalsIgnoreCase("replOpen_Rev")) {
					result.setReplOpen_Rev(value);
				}

				if (label.equalsIgnoreCase("replOpen_Iof")) {
					result.setReplOpen_Iof(value);
				}

				if (label.equalsIgnoreCase("replOpen_Rpo")) {
					result.setReplOpen_Rpo(value);
				}

				if (label.equalsIgnoreCase("replOpen_Dim")) {
					result.setReplOpen_Dim(value);
				}

				if (label.equalsIgnoreCase("dvwp")) {
					result.setDvwp(value);
				}

				if (label.equalsIgnoreCase("dlbm")) {
					result.setDlbm(value);
				}

				if (label.equalsIgnoreCase("daysOpen_Cost")) {
					result.setDaysOpen_Cost(value);
				}

				if (label.equalsIgnoreCase("daysPreg_Cost")) {
					result.setDaysPreg_Cost(value);
				}
			}
			
			rs.close();
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getOpenRpo(int fileNo, int curr, int lact, int season){
		double result[] = new double[Constants.MIM + 1];
		
		int mpreg = 0;
		int openPreg = 1;
		
		String query = "select Months_In_Milk MIM, Season"+season+" Season from dbo.DVIP_OUT_RPO_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? and Months_Pregnant = ? and Open_Or_Preg = ? order by CONVERT(INT, Months_In_Milk)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			pStmt.setInt(colIndex++, mpreg);
			pStmt.setInt(colIndex++, openPreg);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1)] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getPregRpo(int fileNo, int curr, int lact, int conceptionMonth, int season){
		double result[] = new double[Constants.MIM + 1];
		
		int mpreg = 0;
		int openPreg = 1;
		
		String query = "select Months_In_Milk MIM, Season"+season+" Season from dbo.DVIP_OUT_RPO_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? and Months_Pregnant != ? and Months_In_Milk - Months_Pregnant = ? and Open_Or_Preg = ? order by CONVERT(INT, Months_In_Milk)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			pStmt.setInt(colIndex++, mpreg);
			pStmt.setInt(colIndex++, conceptionMonth - 1);
			pStmt.setInt(colIndex++, openPreg);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1)] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getPregValue(int fileNo, int curr, int lact, int conceptionMonth, int season){
		double result[] = new double[Constants.MIM + 1];
		
		int mpreg = 0;
		int openPreg = 2;
		
		String query = "select Months_In_Milk MIM, Season"+season+" Season from dbo.DVIP_OUT_RPO_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? and Months_Pregnant != ? and Months_In_Milk - Months_Pregnant = ? and Open_Or_Preg = ? order by CONVERT(INT, Months_In_Milk)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			pStmt.setInt(colIndex++, mpreg);
			pStmt.setInt(colIndex++, conceptionMonth - 1);
			pStmt.setInt(colIndex++, openPreg);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1)] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getNewPregValue(int fileNo, int curr, int lact, int season){
		double result[] = new double[Constants.MIM + 1];
		
		int mpreg = 0;
		int openPreg = 2;
		
		String query = "select Months_In_Milk MIM, Season"+season+" Season from dbo.DVIP_OUT_RPO_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? and Months_Pregnant = ? and Open_Or_Preg = ? order by CONVERT(INT, Months_In_Milk)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			pStmt.setInt(colIndex++, mpreg);
			pStmt.setInt(colIndex++, openPreg);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1)] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getInsValue(int fileNo, int curr, int lact, int season){
		double result[] = new double[Constants.MIM + 1];
		
		String query = "select Months_In_Milk MIM, Season"+season+" Season from dbo.DVIP_OUT_INS_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? order by CONVERT(INT, Months_In_Milk)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1) + 1] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getPregDelValue(int fileNo, int curr, int lact, int conceptionMonth, int season){
		double result[] = new double[Constants.MIM + 1];
		
		int openPreg = 2;
		
		String query = "select Months Months, Season"+season+" Season from dbo.DVIP_OUT_DEL_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? and Months_In_Milk = ? and Open_Or_Preg = ? order by CONVERT(INT, Months)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			pStmt.setInt(colIndex++, conceptionMonth);
			pStmt.setInt(colIndex++, openPreg);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1) - 1] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public double[] getOpenDelValue(int fileNo, int curr, int lact, int conceptionMonth, int season){
		double result[] = new double[Constants.MIM + 1];
		
		int openPreg = 1;
		
		String query = "select Months Months, Season"+season+" Season from dbo.DVIP_OUT_DEL_DATA where File_No = ? and Current_Milk_Level = ? and Lactation_No = ? and Months_In_Milk = ? and Open_Or_Preg = ? order by CONVERT(INT, Months)";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			int colIndex = 1;
			pStmt.setInt(colIndex++, fileNo);
			pStmt.setInt(colIndex++, curr);
			pStmt.setInt(colIndex++, lact);
			pStmt.setInt(colIndex++, conceptionMonth);
			pStmt.setInt(colIndex++, openPreg);
			rs = pStmt.executeQuery();
			
			while(rs.next()){
				result[rs.getInt(1) - 1] = rs.getDouble(2);
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	public boolean deleteAllOutputs(int fileNo){
		boolean result = true;
		
		Connection sqlConnection = null;
		CallableStatement proc = null;
	    
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			proc = sqlConnection.prepareCall("{ call dbo.Delete_results(?) }");
			
			proc.setInt(1, fileNo);
		    proc.execute();
		    
			proc.close();
			sqlConnection.close();
		} catch (ClassNotFoundException e1) {
			result = false;
			e1.printStackTrace();
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	
	/*
	public boolean deleteResult(int fileNo){
		boolean success = true;
		
		String query = "DELETE FROM dbo.DVIP_OUT_RESULT WHERE File_No = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			prepStmt = sqlConnection.prepareStatement(query);
			
			prepStmt.setInt(1, fileNo);
			prepStmt.executeUpdate();
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			success = false;
			e.printStackTrace();
		}
		
		return success;
	}
	

	
	public boolean deletePolRecord(int fileNo){
		boolean success = true;
		
		String query = "DELETE FROM dbo.DVIP_OUT_POL_DATA WHERE File_No = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			prepStmt = sqlConnection.prepareStatement(query);
			
			prepStmt.setInt(1, fileNo);
			prepStmt.executeUpdate();
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			success = false;
			e.printStackTrace();
		}
		
		return success;
	}
	
	public boolean deleteRpoRecord(int fileNo){
		boolean success = true;
		
		String queryRpo = "DELETE FROM dbo.DVIP_OUT_RPO_DATA WHERE File_No = ?";
		String queryIns = "DELETE FROM dbo.DVIP_OUT_INS_DATA WHERE File_No = ?";
		String queryBioMilkYield = "DELETE FROM dbo.DVIP_OUT_BIO_MILKYIELD_DATA WHERE File_No = ?";
		String queryBioSell = "DELETE FROM dbo.DVIP_OUT_BIO_SELL_DATA WHERE File_No = ?";
		
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			
			prepStmt = sqlConnection.prepareStatement(queryRpo);
			prepStmt.setInt(1, fileNo);
			prepStmt.addBatch();
			
			prepStmt = sqlConnection.prepareStatement(queryIns);
			prepStmt.setInt(1, fileNo);
			prepStmt.addBatch();
			
			prepStmt = sqlConnection.prepareStatement(queryBioMilkYield);
			prepStmt.setInt(1, fileNo);
			prepStmt.addBatch();
			
			prepStmt = sqlConnection.prepareStatement(queryBioSell);
			prepStmt.setInt(1, fileNo);
			prepStmt.addBatch();
			
			prepStmt.executeBatch();
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			success = false;
			e.printStackTrace();
		}
		
		return success;
	}
	
	public boolean deleteDelRecord(int fileNo){
		boolean success = true;
		
		String query = "DELETE FROM dbo.DVIP_OUT_DEL_DATA WHERE File_No = ?";
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement prepStmt = null;
		
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			prepStmt = sqlConnection.prepareStatement(query);
			
			prepStmt.setInt(1, fileNo);
			prepStmt.executeUpdate();
			
			prepStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			success = false;
			e.printStackTrace();
		}
		
		return success;
	}
	*/
	
}
